package com.hbnu.dao;

import com.hbnu.entity.UserWordBook;
import com.hbnu.entity.Word;
import com.hbnu.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserWordBookDAO {
    // 创建自定义单词本
    public boolean createUserWordBook(int userId, String bookName) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO user_word_book (user_id, name) VALUES (?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            pstmt.setString(2, bookName);

            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
        }
    }

    // 查询用户的所有自定义单词本
    public List<UserWordBook> getUserWordBooks(int userId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<UserWordBook> bookList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT id, name, create_time FROM user_word_book WHERE user_id = ? ORDER BY create_time DESC";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                UserWordBook book = new UserWordBook();
                book.setId(rs.getInt("id"));
                book.setName(rs.getString("name"));
                book.setCreateTime(rs.getTimestamp("create_time"));
                bookList.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        }
        return bookList;
    }

    // 向自定义单词本添加单词
    public boolean addWordToUserBook(int bookId, int wordId) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO user_book_word (user_book_id, word_id) VALUES (?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, bookId);
            pstmt.setInt(2, wordId);

            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
        }
    }

    // 查询自定义单词本中的单词
    public List<Word> getWordsFromUserBook(int bookId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Word> wordList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT w.* FROM word w JOIN user_book_word ubw ON w.id = ubw.word_id WHERE ubw.user_book_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, bookId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Word word = new Word();
                word.setId(rs.getInt("id"));
                word.setWord(rs.getString("word"));
                word.setPhonetic(rs.getString("phonetic"));
                word.setDefinition(rs.getString("definition"));
                word.setExample(rs.getString("example"));
                wordList.add(word);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        }
        return wordList;
    }
}